<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Class Mbooking extends MY_Model
{
    public function __construct()
    {
        parent::__construct();
    }
   
    public function getBookingDB($number,$offset,$label_id = 0,$zone_id = 0,$time = 0)
    {
        $limit = $offset ? intval($offset) : '0';
        $sql  = '';
        /*$sql = "SELECT r.id as rid, b.id AS bid, r.name as name_res,
		ra.address as res_address, 
		b.name, b.phone,b.time_booking, b.improved, b.created 
		FROM restaurant as r RIGHT JOIN restaurant_address as ra ON r.id = ra.restaurant_id 
		RIGHT JOIN booking as b ON b.restaurant_id = r.id WHERE 1=1 ";
		*/
		// Cau tren sai, thay bang cau nay, nhatnv
		$sql = 'SELECT b.id AS bid, r.id AS rid, b.`name`, b.phone, b.email, b.time_booking, b.improved, r.`name` AS name_res, ra.address AS res_address FROM booking AS b INNER JOIN restaurant AS r ON b.restaurant_id = r.id LEFT JOIN restaurant_address AS ra ON b.address_id = ra.id WHERE 1 = 1 ';
		
		$keyword = $this->getParamString('keyword');
        if(!in_array($keyword, array(null, ''))){
            $sql .= "AND ( r.`name` LIKE '%".$keyword."%' || ra.`address` LIKE '%".$keyword."%'  )";
        }
        
        /* Filter */
		if(!in_array($label_id, array(null, '', '0', 0))){
            $sql .= " AND b.restaurant_id = ".$label_id;
        }
        if(!in_array($zone_id, array(null, '', '0', 0))){
            $sql .= " AND ra.id_zone = ".$zone_id;
        }
        if(!in_array($time, array(null, '', '0', 0))){
            $sql .= " AND b.time_booking like '%".$time."%'";
        }
        $sql .= " ORDER BY b.improved ASC, bid DESC";//die($sql);
        
        $xdata = " LIMIT ".$limit.",".$number." ";
        $xs = $this->db->query($sql.$xdata)->result_object();
        $count = $this->db->query($sql)->num_rows();
        return array('data' => $xs, 'count' => $count);  
    }

	public function deleteBookingDb($id){
        $this->db->delete('booking', array('id' => $id)); 
        return 200;
	}
	
	public function getBookingInfo($id){
	
		/*return $this->db->query("SELECT r.id as rid, r.name as name_res,
		ra.id as raid, ra.address as res_address, 
		b.name, b.phone,b.time_booking, b.improved, b.created, b.peoples, b.required, b.email
		FROM restaurant as r JOIN restaurant_address as ra ON r.id = ra.restaurant_id 
		JOIN booking as b ON b.restaurant_id = r.id WHERE b.id = ".$id)->row_object();
		*/
		
		// Cau tren sai thay bang cau nay, nhatnv
		return $this->db->query('SELECT b.id AS bid, r.id AS rid, b.`name`, b.phone, b.email, b.time_booking, b.improved, r.`name` AS name_res, ra.address AS res_address, ra.id as raid, b.peoples, b.required, b.created FROM booking AS b INNER JOIN restaurant AS r ON b.restaurant_id = r.id LEFT JOIN restaurant_address AS ra ON b.address_id = ra.id WHERE 1 = 1 AND b.id = '.$id)->row_object();
	}
	
	public function editBooking($id,$restaurant_id,$address_id,$name,$phone,$email,$time_booking,$peoples,$required,$improved){
		$data = array(
			'restaurant_id' => $restaurant_id,
			'address_id' => $address_id,
            'name' => $name,
			'phone' => $phone,
			'email' => $email,
			'time_booking' => $time_booking,
			'peoples' => $peoples,
			'required' => $required,
			'improved' => $improved
        );
        $this->db->update('booking', $data, "id = ".$id);
        return 200;
	}
	
	public function optionRestaurant($id)
    {
        $data = $this->db->query('SELECT id, name FROM restaurant ORDER BY id ASC')->result_object();
        $html = '<option value = "">Chọn nhà hàng</option>';
        foreach($data AS $el=>$le)
        {
            if($le->id == $id)
            {
                $selected = 'selected="selected"';
            }else{
                $selected = '';
            }
            $html .= '<option value="'.$le->id.'" '.$selected.'>'.$le->name.'</option>';
        }
        return $html;
    }
	
	public function optionAddress($id)
    {
        $data = $this->db->query('SELECT id, address FROM restaurant_address')->result_object();
        $html = '<option value = "">Chọn địa điểm</option>';
        foreach($data AS $el=>$le)
        {
            if($le->id == $id)
            {
                $selected = 'selected="selected"';
            }else{
                $selected = '';
            }
            $html .= '<option value="'.$le->id.'" '.$selected.'>'.$le->address.'</option>';
        }
        return $html;
    }
	
	public function optionStar($id)
    {
        $html = '<option value = "">Chọn số sao</option>';
        for ( $i = 1; $i <=5; $i++  ) 
        {
            if($i == $id)
            {
                $selected = 'selected="selected"';
            }else{
                $selected = '';
            }
            $html .= '<option value="'.$i.'" '.$selected.'>'.$i.' Sao</option>';
        }
        return $html;
    }   
	
    
    public function getListLabel()
    {
        $sql = "SELECT id,name FROM restaurant";
        $data = $this->db->query($sql)->result_array();
        return $data;
    }
    public function getListZone()
    {
        $sql = "SELECT id,title AS name FROM zone";
        $data = $this->db->query($sql)->result_array();
        return $data;
    }
    
    public function reportData($label_id = 0,$zone_id = 0,$time = 0)
    {
        $sql = 'SELECT b.id AS bid, r.id AS rid, b.`name`, b.phone, b.email, b.time_booking, b.improved, r.`name` AS name_res, ra.address AS res_address FROM booking AS b INNER JOIN restaurant AS r ON b.restaurant_id = r.id LEFT JOIN restaurant_address AS ra ON b.address_id = ra.id WHERE 1 = 1 ';
		
		$keyword = $this->getParamString('keyword');
        if(!in_array($keyword, array(null, ''))){
            $sql .= "AND ( r.`name` LIKE '%".$keyword."%' || ra.`address` LIKE '%".$keyword."%'  )";
        }
        
        /* Filter */
		if(!in_array($label_id, array(null, '', '0', 0))){
            $sql .= " AND b.restaurant_id = ".$label_id;
        }
        if(!in_array($zone_id, array(null, '', '0', 0))){
            $sql .= " AND ra.id_zone = ".$zone_id;
        }
        if(!in_array($time, array(null, '', '0', 0))){
            $sql .= " AND b.time_booking like '%".$time."%'";
        }
        $sql .= " ORDER BY b.improved ASC, b.time_booking ASC, bid DESC";//die($sql);
        
        $query = $this->db->query($sql);
        $count = $query->num_rows();
        $data = $query->result_object();
        
        return array('data' => $data, 'count' => $count);  
    }
    
    public function getreportLabel($id)
    {
        $data = $this->db->query("SELECT name FROM restaurant WHERE id = ".$id)->row_object();
        return $data->name;
    }
    public function getreportZone($id)
    {
        $data = $this->db->query("SELECT title FROM zone WHERE id = ".$id)->row_object();
        return $data->title;
    }
}
 